/********************************************************************************
    Copyright (C) Binod Nepal, Planet Earth Solutions Pvt. Ltd., Kathmandu.
	Released under the terms of the GNU General Public License, GPL, 
	as published by the Free Software Foundation, either version 3 
	of the License, or (at your option) any later version.
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  
    See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
***********************************************************************************/

SET NOCOUNT ON

USE master
GO
IF EXISTS(SELECT 1 FROM sys.databases WHERE name = 'Messaging') 
BEGIN
	ALTER DATABASE [Messaging] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
	DROP DATABASE [Messaging]
	PRINT 'Messaging database was dropped';
END;
GO

CREATE DATABASE Messaging;
GO
USE Messaging;

GO

CREATE TABLE dbo.Applications
(
	ApplicationId			integer NOT NULL CONSTRAINT Applications_PK PRIMARY KEY,
	ApplicationName			character varying(128) NOT NULL,
	EmailFrom				character varying(128) NOT NULL,
	Host					character varying(128) NOT NULL,
	Port					integer NOT NULL,
	EnableSsl				bit NOT NULL,
	UserName				character varying(128) NOT NULL,
	Password				character varying(128) NOT NULL	
);	

INSERT INTO dbo.Applications(ApplicationId, ApplicationName, EmailFrom, Host, Port, EnableSsl, UserName, Password)
SELECT 0, 'PMS', 'PES Messaging Services', 'smtp.gmail.com', 587, 1, 'bot@planetearthsolution.com', '' UNION ALL
SELECT 1, 'MixNP', 'MixNP Classifieds', 'smtp.gmail.com', 587, 1, 'bot@planetearthsolution.com', '';

GO

CREATE TABLE dbo.OutgoingTexts
(
	OutgoingTextId			uniqueidentifier NOT NULL CONSTRAINT OutgoingTexts_PK PRIMARY KEY CONSTRAINT OutgoingTexts_PK_DF DEFAULT(NEWSEQUENTIALID()),
	ApplicationId			integer NOT NULL CONSTRAINT OutgoingTexts_Applications_FK FOREIGN KEY REFERENCES dbo.Applications(ApplicationId),
	Domain					national character varying(128) NOT NULL, --MixNP.User.Registration
	CellNumber				character(10) NOT NULL,
	Message					national character varying(128) NOT NULL,
	QueuedOn				datetime NOT NULL CONSTRAINT OutgoingTexts_QueuedOn_DF DEFAULT(GETUTCDATE()),
	Sent					bit NOT NULL CONSTRAINT OutgoingTexts_Sent_DF DEFAULT(0),
	SentOn					datetime NOT NULL CONSTRAINT OutgoingTexts_SentOn_DF DEFAULT(GETUTCDATE())
);

GO

GO

CREATE TABLE dbo.IncomingTexts
(
	IncomingTextId			uniqueidentifier NOT NULL CONSTRAINT IncomingTexts_PK PRIMARY KEY CONSTRAINT IncomingTexts_PK_DF DEFAULT(NEWSEQUENTIALID()),
	CellNumber				national character varying(50) NOT NULL,
	Message					national character varying(1000) NOT NULL,
	ReceivedOn				datetime NOT NULL CONSTRAINT IncomingTexts_ReceivedOn_DF DEFAULT(GETUTCDATE())
);

GO

CREATE TABLE dbo.OutgoingEmails
(
	OutgoingEmailId			uniqueidentifier NOT NULL CONSTRAINT OutgoingEmails_PK PRIMARY KEY CONSTRAINT OutgoingEmails_PK_DF DEFAULT(NEWSEQUENTIALID()),
	ApplicationId			integer NOT NULL CONSTRAINT OutgoingEmails_Applications_FK FOREIGN KEY REFERENCES dbo.Applications(ApplicationId),
	Domain					national character varying(128) NOT NULL, --MixNP.User.Registration, MixNP.Item, MixNP.Item.Comment
	PKValue					national character varying(128) NULL CONSTRAINT OutgoingEmails_DomainId_DF DEFAULT(''),	
	EmailAddress			national character varying(128) NOT NULL,
	Subject					national character varying(128) NOT NULL,
	Message					national character varying(MAX) NOT NULL,
	SentBy					national character varying(128) NOT NULL,
	AddedOn					datetime NOT NULL CONSTRAINT OutgoingEmails_AddedOn_DF DEFAULT(GETUTCDATE()),
	Sent					bit NOT NULL CONSTRAINT OutgoingEmails_Sent_DF DEFAULT(0),
	SentOn					datetime NULL
);

GO

CREATE INDEX OutgoingEmails_Sent_INX
ON dbo.OutgoingEmails(Sent);

GO

CREATE VIEW dbo.OutgoingEmailView
AS
SELECT     dbo.Applications.ApplicationId, dbo.OutgoingEmails.OutgoingEmailId, dbo.OutgoingEmails.Domain, dbo.OutgoingEmails.PKValue, dbo.OutgoingEmails.EmailAddress, dbo.OutgoingEmails.Subject, 
                      dbo.OutgoingEmails.Message, dbo.OutgoingEmails.SentBy, dbo.OutgoingEmails.AddedOn, dbo.OutgoingEmails.Sent, dbo.OutgoingEmails.SentOn, dbo.Applications.ApplicationName, 
                      dbo.Applications.EmailFrom, dbo.Applications.Host, dbo.Applications.Port, dbo.Applications.EnableSsl, dbo.Applications.UserName, dbo.Applications.Password
FROM         dbo.Applications INNER JOIN
                      dbo.OutgoingEmails ON dbo.Applications.ApplicationId = dbo.OutgoingEmails.ApplicationId;

GO

CREATE VIEW dbo.OutgoingTextView
AS
SELECT     dbo.Applications.ApplicationId, dbo.Applications.ApplicationName, dbo.OutgoingTexts.OutgoingTextId, dbo.OutgoingTexts.Domain, dbo.OutgoingTexts.CellNumber, dbo.OutgoingTexts.Message, 
                      dbo.OutgoingTexts.QueuedOn, dbo.OutgoingTexts.Sent, dbo.OutgoingTexts.SentOn
FROM         dbo.OutgoingTexts INNER JOIN
                      dbo.Applications ON dbo.OutgoingTexts.ApplicationId = dbo.Applications.ApplicationId;

GO

CREATE PROCEDURE SysMail
	@EmailAddress national character varying(128),
	@Subject national character varying(128),
	@Message national character varying(MAX)
AS
BEGIN
	INSERT INTO dbo.OutgoingEmails(ApplicationId, Domain, PKValue, EmailAddress, Subject, Message, SentBy)
	SELECT 0, 'SysMail', NULL, @EmailAddress, @Subject, @Message, 'PES Messaging Services';
END;

GO